﻿using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;
using Microsoft.Office.Server.UserProfiles;
using System.Data;
using Microsoft.Office.Server;
using System.Text;
using Square.Activity;
using System.Data.SqlClient;
using System.Reflection;

namespace Square.CONTROLTEMPLATES.Square
{
    public partial class ColleaguesRequset : UserControl
    {
        #region ## DataTableColumns
        private string _idx = "IDX";
        private string _id = "ID";
        private string _displayName = "DISPLAYNAME";
        private string _recordID = "RECORDID";
        private string _colleagueRecordID = "COLLEAGUERECORDID";
        private string _pictureURL = "PICTUREURL";
        private string _personalURL = "PERSONALURL";
        #endregion

        #region ## Properties
        private string _siteUrl { get { return SPContext.Current.Site.Url + "/my"; } }
        private string _webUrl { get { return SPContext.Current.Web.Url; } }
        private string _listUrl { get { return "/my/Lists/Sq_Colleagues"; } }
        private string _listName { get { return "Sq_Colleagues"; } }

        public string _accountName
        {   
            get
            {
                if (Request.QueryString["accountname"] != null)
                    return Request.QueryString["accountname"];
                else
                    return this.Page.User.Identity.Name;
                    //return hd_accountName.Value;
            }
        }

        public enum SearchType
        {
            none = 0, // 없음
            group = 1, // 그룹
        }
        
        public UserProfile _pageUserProfile;
        public UserProfile _colleagueProfile;
        public string _pageUserRecordID { get; set; }
        public string siteUrl { get; set; }
        public string accountName { get; set; }
        
        SquareActivityHelper sHelper;
        #endregion

        protected void Page_Load(object sender, EventArgs e)
        {
   
            if (!IsPostBack)
            {
                hd_accountName.Value = _accountName;
                hd_siteUrl.Value = _siteUrl;
                RequestList();
                ResponseList();
            }
            siteUrl = _siteUrl;
            if (_siteUrl == null) siteUrl = hd_siteUrl.Value;
            accountName = _accountName;
            if (_accountName == null) accountName = hd_accountName.Value;

        }

        #region ## 요청 목록 호출
        public void RequestList()
        {
            try
            {
                siteUrl = _siteUrl;
                if (_siteUrl == null) siteUrl = hd_siteUrl.Value;
                accountName = _accountName;
                if (_accountName == null) accountName = hd_accountName.Value;

                DataTable dt_Request = new DataTable();

                SPSecurity.RunWithElevatedPrivileges(delegate() 
                {
                    using (SPSite _site = new SPSite(_siteUrl))
                    {
                        using (SPWeb _web = _site.OpenWeb())
                        {
                            SPList _list = _web.GetList(_listUrl);
                            //SPList _list = _web.Lists[_listName];
                            
                            SPServiceContext context = SPServiceContext.GetContext(_site);
                            //ServerContext context = ServerContext.GetContext(_site);
                            UserProfileManager upm = new UserProfileManager(context);

                            if (upm.UserExists(accountName))
                            {
                                _pageUserProfile = upm.GetUserProfile(accountName);
                                UserProfile[] impersUser = _pageUserProfile.Colleagues.GetCommonColleagues();
                                if (_pageUserProfile != null)
                                {
                                    _pageUserRecordID = _pageUserProfile.RecordId.ToString();
                                }

                                SPQuery sQuery = new SPQuery();
                                sQuery.ViewFields = SPQueryViewFields();
                                sQuery.Query = SPQueryStringRS(_pageUserRecordID);

                                SPListItemCollection _listItems = _list.GetItems(sQuery);

                                if (_listItems.Count > 0)
                                {
                                    dt_Request.Columns.Add(_idx);
                                    dt_Request.Columns.Add(_id);
                                    dt_Request.Columns.Add(_displayName);
                                    dt_Request.Columns.Add(_recordID);
                                    dt_Request.Columns.Add(_pictureURL);
                                    dt_Request.Columns.Add(_colleagueRecordID);
                                    dt_Request.Columns.Add(_personalURL);

                                    foreach (SPListItem _item in _listItems)
                                    {
                                        UserProfile _colleaguesProfile = upm.GetUserProfile(long.Parse(_item["RecordId"].ToString()));
                                        string[] strName = _colleaguesProfile.DisplayName.Split('\\');
                                        DataRow dr = dt_Request.NewRow();
                                        dr[_idx] = _item["Title"].ToString();
                                        dr[_id] = _item.ID.ToString();
                                        dr[_displayName] = strName[1];
                                        dr[_recordID] = _pageUserRecordID;
                                        if (_colleaguesProfile["PictureURL"].Value == null)
                                            dr[_pictureURL] = "/_layouts/images/O14_person_placeHolder_32.png";
                                        else
                                            dr[_pictureURL] = _colleaguesProfile["PictureURL"].Value;
                                        dr[_colleagueRecordID] = _item["ColleagueRecordId"];
                                        dr[_personalURL] = _colleaguesProfile.PersonalUrl;

                                        dt_Request.Rows.Add(dr);
                                    }
                                }
                            }

                            rp_RequestList.DataSource = dt_Request;
                            rp_RequestList.DataBind();

                        }
                    }
                });
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
        }
        #endregion

        #region ## 응답 목록 호출
        public void ResponseList()
        {
            try
            {
                siteUrl = _siteUrl;
                if (_siteUrl == null) siteUrl = hd_siteUrl.Value;
                accountName = _accountName;
                if (_accountName == null) accountName = hd_accountName.Value;

                DataTable dt_Request = new DataTable();

                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    using (SPSite _site = new SPSite(_siteUrl))
                    {
                        using (SPWeb _web = _site.OpenWeb())
                        {
                            SPList _list = _web.GetList(_listUrl);
                            //SPList _list = _web.Lists[_listName];
                            //SPServiceContext
                            SPServiceContext context = SPServiceContext.GetContext(_site);
                            //ServerContext context = ServerContext.GetContext(_site);
                            UserProfileManager upm = new UserProfileManager(context);

                            if (upm.UserExists(accountName))
                            {
                                _pageUserProfile = upm.GetUserProfile(accountName);
                                UserProfile[] impersUser = _pageUserProfile.Colleagues.GetCommonColleagues();
                                if (_pageUserProfile != null)
                                {
                                    _pageUserRecordID = _pageUserProfile.RecordId.ToString();
                                }

                                SPQuery sQuery = new SPQuery();
                                sQuery.ViewFields = SPQueryViewFields();
                                sQuery.Query = SPQueryStringRQ(_pageUserRecordID);

                                SPListItemCollection _listItems = _list.GetItems(sQuery);

                                if (_listItems.Count > 0)
                                {
                                    dt_Request.Columns.Add(_idx);
                                    dt_Request.Columns.Add(_id);
                                    dt_Request.Columns.Add(_displayName);
                                    dt_Request.Columns.Add(_recordID);
                                    dt_Request.Columns.Add(_pictureURL);
                                    dt_Request.Columns.Add(_colleagueRecordID);
                                    dt_Request.Columns.Add(_personalURL);

                                    foreach (SPListItem _item in _listItems)
                                    {
                                        UserProfile _colleaguesProfile = upm.GetUserProfile(long.Parse(_item["ColleagueRecordId"].ToString()));
                                        string[] strName = _colleaguesProfile.DisplayName.Split('\\');
                                        DataRow dr = dt_Request.NewRow();
                                        dr[_idx] = _item["Title"].ToString();
                                        dr[_id] = _item.ID.ToString();
                                        dr[_displayName] = strName[1];
                                        dr[_recordID] = _pageUserRecordID;
                                        if (_colleaguesProfile["PictureURL"].Value == null)
                                            dr[_pictureURL] = "/_layouts/images/O14_person_placeHolder_32.png";
                                        else
                                            dr[_pictureURL] = _colleaguesProfile["PictureURL"].Value;
                                        dr[_colleagueRecordID] = _item["ColleagueRecordId"];
                                        dr[_personalURL] = _colleaguesProfile.PersonalUrl;

                                        dt_Request.Rows.Add(dr);
                                    }
                                }
                            }

                            rp_ResponseList.DataSource = dt_Request;
                            rp_ResponseList.DataBind();

                        }
                    }
                });
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
        
        }
        #endregion

        #region ## SPQueryViewFields
        public string SPQueryViewFields()
        { 
            StringBuilder sb = new StringBuilder();
            sb.Append("<FieldRef Name=\"Title\" />");
            sb.Append("<FieldRef Name=\"RecordId\" />");
            sb.Append("<FieldRef Name=\"ColleagueRecordId\" />");
            sb.Append("<FieldRef Name=\"IsEnable\" />");
            sb.Append("<FieldRef Name=\"GroupTitle\" />");
            sb.Append("<FieldRef Name=\"Created\" />");
            return sb.ToString();
        }
        #endregion

        #region ## SPQueryString
        public string SPQueryStringRQ(string recordid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<OrderBy>");
            sb.Append("<FieldRef Name=\"Created\" Ascending=\"False\" />");
            sb.Append("</OrderBy>");
            sb.Append("<Where>");
            sb.Append(" <And>");
            sb.Append("     <Eq>");
            sb.Append("         <FieldRef Name=\"RecordId\" />");
            sb.AppendFormat("   <Value Type=\"Text\">{0}</Value>", recordid);
            sb.Append("     </Eq>");
            sb.Append("     <Eq>");
            sb.Append("         <FieldRef Name=\"IsEnable\" />");
            sb.Append("         <Value Type=\"Text\">N</Value>");
            sb.Append("     </Eq>");
            sb.Append(" </And>");
            sb.Append("</Where>");
            return sb.ToString();
        }

        public string SPQueryStringRS(string recordid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<OrderBy>");
            sb.Append("<FieldRef Name=\"Created\" Ascending=\"False\" />");
            sb.Append("</OrderBy>");
            sb.Append("<Where>");
            sb.Append(" <And>");
            sb.Append("     <Eq>");
            sb.Append("         <FieldRef Name=\"ColleagueRecordId\" />");
            sb.AppendFormat("   <Value Type=\"Text\">{0}</Value>", recordid);
            sb.Append("     </Eq>");
            sb.Append("     <Eq>");
            sb.Append("         <FieldRef Name=\"IsEnable\" />");
            sb.Append("         <Value Type=\"Text\">N</Value>");
            sb.Append("     </Eq>");
            sb.Append(" </And>");
            sb.Append("</Where>");
            return sb.ToString();
        }
        #endregion

        #region ## 요청 수락 / 거절
        /// <summary>
        /// ## 요청 수락
        /// </summary>
        public void lbtn_RequestOK_Click(object sender, EventArgs e)
        {
            string rId = hd_RequestID.Value;
            string rName = hd_AcceptColleagueName.Value;
            string title = string.Empty;
            string groupTitle = string.Empty;
            string isWorkgroup = string.Empty;
            string itemSecurity = string.Empty;
            string isColleagues = string.Empty;
            string cID = string.Empty;
            string pID = string.Empty;

            try
            {
                SPSecurity.RunWithElevatedPrivileges(delegate() 
                {
                    using (SPSite _site = new SPSite(_siteUrl))
                    {
                        using (SPWeb _web = _site.OpenWeb())
                        {
                            SPList _list = _web.GetList(_listUrl);
                            //SPList _list = _web.Lists[_listName];

                            SPListItem _item = _list.GetItemById(Convert.ToInt32(rId));
                            _item["IsEnable"] = "Y";
                            title = _item["Title"].ToString();
                            pID = _item["RecordId"].ToString();
                            cID = _item["ColleagueRecordId"].ToString();
                            groupTitle = _item["GroupTitle"].ToString();
                            isWorkgroup = _item["IsWorkgroup"].ToString();
                            itemSecurity = _item["ItemSecurity"].ToString();
                            isColleagues = _item["IsColleagues"].ToString();
                            _web.AllowUnsafeUpdates = true;
                            _item.Update();
                            _list.Update();
                            _web.AllowUnsafeUpdates = false;

                            addColleaguesOk(title, groupTitle, isWorkgroup, itemSecurity, isColleagues, rName, cID, pID);

                            SPServiceContext context = SPServiceContext.GetContext(_site);
                            UserProfileManager upm = new UserProfileManager(context);

                            UserProfile _cUser = upm.GetUserProfile(long.Parse(pID));

                            add_SPGroup(_web, _cUser);

                            bool bSave = false;
                            SquareActivities aType = new SquareActivities();
                            aType = SquareActivities.SquarePeopleAccept;

                            sHelper = new SquareActivityHelper();
                            //<img src=\"" + userInfo["PictureURL"].Value + "\" class=\"list_people_img\" />"
                            //bSave = sHelper.SaveActivity(this.Page, aType, rName  + "님의 요청을 수락하습니다.");
                            bSave = sHelper.SaveActivity(this.Page, aType, " has accepted " + "<a href=\"" + _cUser.PersonalUrl + "\" target=\"_blank\">" + rName + "</a>" + "'s request.");

                            RequestList();
                            ResponseList();
                        }
                    }
                });
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
        }

        /// <summary>
        /// ## 요청 거절
        /// </summary>
        public void lbtn_RequestCancel_Click(object sender, EventArgs e)
        {
            string[] userName = _accountName.Split('\\');

            string rId = hd_RequestID.Value;

            try
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    using (SPSite _site = new SPSite(_siteUrl))
                    {
                        using (SPWeb _web = _site.OpenWeb())
                        {
                            //Site의 그룹을 가져온다.
                            SPGroupCollection groups = _web.SiteGroups;

                            //권한을 부여할 그룹또는 사용자를 가져온다.
                            string spGroupName = string.Empty;
                            spGroupName = "FrGroup_" + userName[1];

                            SPList _list = _web.GetList(_listUrl);
                            //SPList _list = _web.Lists[_listName];

                            SPServiceContext context = SPServiceContext.GetContext(_site);
                            //ServerContext context = ServerContext.GetContext(_site);
                            UserProfileManager upm = new UserProfileManager(context);

                            SPListItem _item = _list.GetItemById(Convert.ToInt32(rId));
                            _pageUserProfile = upm.GetUserProfile(accountName);
                            UserProfile _colleaguesProfile = upm.GetUserProfile(long.Parse(_item["ColleagueRecordId"].ToString()));
                            DeleteColleague(_siteUrl, _accountName, _colleaguesProfile.DisplayName);

                            _web.AllowUnsafeUpdates = true;
                            _item.Delete();
                            _list.Update();
                            _web.AllowUnsafeUpdates = false;

                            SPUser _deleteUser = _web.EnsureUser(_colleaguesProfile.DisplayName);
                            ClearMemberInGroup(spGroupName, _web, _deleteUser);

                            string[] _colleagueName = _colleaguesProfile.DisplayName.Split('\\');

                            bool bSave = false;
                            SquareActivities aType = new SquareActivities();
                            aType = SquareActivities.SquarePeopleAccept;

                            sHelper = new SquareActivityHelper();
                            //<img src=\"" + userInfo["PictureURL"].Value + "\" class=\"list_people_img\" />"
                            //bSave = sHelper.SaveActivity(this.Page, aType, rName  + "님의 요청을 수락하습니다.");
                            bSave = sHelper.SaveActivity(this.Page, aType, " had to cancel a request for " + "<a href=\"" + _colleaguesProfile.PersonalUrl + "\" target=\"_blank\">" + _colleagueName[1] + "</a>");


                            RequestList();
                            ResponseList();
                        }
                    }
                });
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
        }
        #endregion

        #region ## DB Data Delete
        /// <summary>
        /// Deletes a colleague relationship.
        /// </summary>
        /// <param name="requestUrl">URL of the SharePoint site to connect to.</param>
        /// <param name="loginName">Login name of a user having access to 
        /// the SharePoint site (for example, DOMAIN\User).</param>
        /// <param name="colleagueLoginName">Login name of the user to add 
        /// to loginName's colleagues (for example, DOMAIN\User).</param>
        public static void DeleteColleague(string requestUrl, string loginName, string colleagueLoginName)
        {
            using (SPSite siteCollection = new SPSite(requestUrl))
            {
                using (SPWeb webSite = siteCollection.OpenWeb())
                {
                    // Retrieve the ServerContext of the site collection.
                    SPServiceContext serverContext = SPServiceContext.GetContext(siteCollection);
                    //ServerContext serverContext = ServerContext.GetContext(siteCollection);

                    // Connect to the UserProfileManager of the SharePoint Web application.
                    UserProfileManager userProfileManager = new UserProfileManager(serverContext);

                    // Check whether the user profile and the colleague user profile exist, and that they are not
                    // the same user.
                    if (userProfileManager.UserExists(loginName) &&
                        userProfileManager.UserExists(colleagueLoginName) &&
                        string.Compare(loginName, colleagueLoginName, true) != 0)
                    {
                        // Retrieve user profiles.
                        UserProfile userProfile = userProfileManager.GetUserProfile(loginName);
                        UserProfile userProfileColleague = userProfileManager.GetUserProfile(colleagueLoginName);

                        // Check whether colleagueLoginName is already a colleague.
                        if (userProfile.Colleagues.IsColleague(userProfileColleague.ID) == true)
                        {
                            userProfile.Colleagues[userProfileColleague].Delete();

                            // Save the modification made to the user profile.
                            userProfile.Commit();
                        }
                    }
                }
            }
        }
        #endregion

        #region ## Request Colleague Add
        /// <summary>
        /// ## Request Colleague Add
        /// </summary>
        /// <param name="tit">Title</param>
        /// <param name="gt">GroupTitle</param>
        /// <param name="wg">IsWorkGroup</param>
        /// <param name="s">IsSecurity</param>
        /// <param name="ic">IsColleague</param>
        /// <param name="rName">ColleagueName</param>
        /// <param name="cId">ColleagueRecordId</param>
        /// <param name="pId">RecordId</param>
        private void addColleaguesOk(string tit, string gt, string wg, string s, string ic, string rName, string cId, string pId)
        {
            try
            {
                using (SPSite _site = new SPSite(_siteUrl))
                {
                    using (SPWeb _web = _site.OpenWeb())
                    {
                        SPServiceContext context = SPServiceContext.GetContext(_site);
                        UserProfileManager upm = new UserProfileManager(context);

                        if (upm.UserExists(_accountName))
                        {
                            _pageUserProfile = upm.GetUserProfile(_accountName);

                            if (_pageUserProfile != null)
                            {
                                SPList _list = _web.GetList(_listUrl);
                                //SPList _list = _web.Lists[_listName];

                                // 기존에 다른 그룹에 등록이 되어 있는 동료 인지 확인한다.

                                if (!string.IsNullOrEmpty(rName))
                                {
                                    // DB 저장
                                    db_Save(rName);
                                    // 리스트 저장
                                    _colleagueProfile = upm.GetUserProfile(rName);
                                    SPListItem _item = _list.Items.Add();

                                    //bool chkGroup = false;
                                    //if (GetColleagueID(_pageUserProfile, _colleagueProfile) != null)
                                    //    chkGroup = true;

                                    //if (!chkGroup) _item["Title"] = GetColleagueID(_pageUserProfile, _colleagueProfile).Tables[0].Rows[0]["Id"];              // DB에 저장되는 Id 
                                    //else _item["Title"] = GetColleagueID(_pageUserProfile, _colleagueProfile).Tables[0].Rows[0]["Id"] + "_" + DateTime.Now.ToString("yyyyMMddhhmmss");  // 없을 경우는 기존 DB에 저장되어 있는 Id_yyyyMMddhhmmss
                                    _item["Title"] = GetColleagueID(_pageUserProfile, _colleagueProfile).Tables[0].Rows[0]["Id"];              // DB에 저장되는 Id 
                                    _item["RecordId"] = cId;  // 현재 페이지의 사용자 RecordId
                                    _item["ColleagueRecordId"] = pId;                // 동료로 추가되는 RecordId
                                    if (hd_IsGroup.Value == "N")                    // DB에 저장되는 GroupType - 현재 사용되지는 않는다.
                                        _item["GroupType"] = "1";
                                    else
                                        _item["GroupType"] = "0";
                                    _item["GroupTitle"] = string.IsNullOrEmpty(gt) ? "General" : gt;      // 선택 박스에서 선택한 그룹 또는 새로 생성된 그룹
                                    _item["IsWorkgroup"] = wg;         // 팀 선택 여부 Y / N
                                    _item["ItemSecurity"] = s;    // 공개 설정 여부 1(모두공개) / 2(내동료) / 4(팀공개) / 8(내관리자) / 16(비공개)
                                    _item["PartitionID"] = _pageUserProfile.Suggestions.GetType().GetProperty("PartitionID", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(_pageUserProfile.Suggestions, null).ToString(); ;     // SharePoint에서 지정한 PartitionID - 아직 사용되지 않는다.
                                    _item["IsEnable"] = "Y";                        // 동료 요청 수락 여부 Y / N - 신규 생성 시 N
                                    _item["IsColleagues"] = ic;        // 팀 그룹 여부 Y / N

                                    _web.AllowUnsafeUpdates = true;
                                    _item.Update();
                                    _web.AllowUnsafeUpdates = false;
                                }
                            }
                        }
                    }
                }
            }
            catch { }
        }

        private void db_Save(string rName)
        {
            //Open SPSite
            using (SPSite site = new SPSite(_siteUrl))
            {
                //Get Server context
                //SPServiceContext
                SPServiceContext context = SPServiceContext.GetContext(site);
                //ServerContext context = ServerContext.GetContext(site);
                //Get UserProfileManager for the site cocntext
                UserProfileManager profileManager = new UserProfileManager(context);
                UserProfile userProfile;
                if (profileManager.UserExists(_accountName))
                {
                    userProfile = profileManager.GetUserProfile(_accountName);

                    //Check whether the colleague profile exists or not
                    if (profileManager.UserExists(_accountName))
                    {
                        ColleagueManager colleagueManager;
                        UserProfile colleagueProfile;
                        colleagueProfile = profileManager.GetUserProfile(rName);
                        colleagueManager = userProfile.Colleagues;
                        //Adds collegue with group type peer. This property is marked as public
                        colleagueManager.Create(colleagueProfile, ColleagueGroupType.General, "", false, Privacy.Public);
                    }
                }
            }
        }
        #endregion

        #region ## DB Item ID
        private DataSet GetColleagueID(UserProfile userProfile, UserProfile colleagueProfile)
        {
            DataSet ColleagueID = new DataSet();
            SqlDataAdapter adapter;
            StringBuilder ColleagueIDquery = new StringBuilder();

            SqlConnection profileConn = new SqlConnection();
            SqlConnection socialConn = new SqlConnection();
            string partitionid = string.Empty;
            string pConn = string.Empty;
            string sConn = string.Empty;

            if (System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"] != null)
            {
                pConn = System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"].ToString();
            }
            if (System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"] != null)
            {
                sConn = System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"].ToString();
            }

            try
            {
                profileConn.ConnectionString = pConn;
                socialConn.ConnectionString = sConn;
                profileConn.Open();
                socialConn.Open();
            }
            catch (Exception ex)
            {
                if (profileConn != null) profileConn.Dispose();
                if (socialConn != null) socialConn.Dispose();
                throw new Exception(ex.Message);
            }
            //ColleagueID를 가지고 온다
            using (SqlCommand command = new SqlCommand())//new SqlCommand("dbo.proc_SocialComments_GetForUser"))
            {
                partitionid = userProfile.Suggestions.GetType().GetProperty("PartitionID", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(userProfile.Suggestions, null).ToString();

                ColleagueIDquery.AppendLine("SELECT Id");
                ColleagueIDquery.AppendLine(" FROM UserColleagues (NOLOCK)");
                ColleagueIDquery.AppendFormat(" WHERE RecordId = '{0}'", userProfile.RecordId);
                ColleagueIDquery.AppendFormat(" and ColleagueRecordId = '{0}'", colleagueProfile.RecordId);
                //query.AppendLine(" and sc.LastModifiedTime >= @fromDate and sc.LastModifiedTime < @toDate");

                command.CommandText = ColleagueIDquery.ToString();
                command.Connection = profileConn;
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(ColleagueID);

            }
            profileConn.Close();
            if (profileConn != null) profileConn.Dispose();
            socialConn.Close();
            if (socialConn != null) socialConn.Dispose();

            return ColleagueID;
        }
        #endregion

        #region ## SPGroup 생성 / 사용자 추가 / 삭제
        private void add_SPGroup(SPWeb _web, UserProfile _cUser)
        {
            string[] userName = _accountName.Split('\\');

            //Site의 그룹을 가져온다.
            SPGroupCollection groups = _web.SiteGroups;

            //권한을 부여할 그룹또는 사용자를 가져온다.
            string spGroupName = string.Empty;
            spGroupName = "FrGroup_" + userName[1];

            SPMember siteOwner = _web.SiteAdministrators[0];
            bool grpExists = false;

            foreach (SPGroup group in groups)
                if (group.Name.ToLower() == spGroupName.ToLower())
                    grpExists = true;

            _web.AllowUnsafeUpdates = true;
            if (!grpExists)
            {
                _web.SiteGroups.Add(spGroupName, siteOwner, null,
                     "Custom Group that I created because I can!");
                _web.Update();

                //get a reference to the group we just created
                SPGroup customGroup = _web.SiteGroups[spGroupName];

                //add the group to the quicklaunch
                _web.Properties["vti_associategroups"] = _web.Properties["vti_associategroups"] + ";" + customGroup.ID.ToString();
                _web.Properties.Update();

                SPUser _colleagueUser = _web.EnsureUser(_cUser.DisplayName);
                customGroup.AddUser(_colleagueUser);

                _web.Update();
            }
            else
            {
                SPGroup _group = _web.SiteGroups[spGroupName];
                SPUser _colleagueUser = _web.EnsureUser(_cUser.DisplayName);
                _group.AddUser(_colleagueUser);
                _web.Update();
            }

            _web.AllowUnsafeUpdates = false;
        }

        //spgroup의 멤버들을 삭제
        private void ClearMemberInGroup(string strSPGroupName, SPWeb _cWeb, SPUser removeuser)
        {
            Guid oSiteID = SPContext.Current.Site.ID;
            Guid oWebID = SPContext.Current.Web.ID;

            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                if (Is_SPGroup_Exist(strSPGroupName, _cWeb))
                {
                    using (SPSite _site = new SPSite(oSiteID))
                    {
                        using (SPWeb _web = _site.OpenWeb(oWebID))
                        {
                            SPGroup SP_WGGroup = _web.SiteGroups[strSPGroupName];
                            SP_WGGroup.RemoveUser(removeuser); 
                        }
                    }
                }
            });
        }

        //spgroup에 해당 멤버가 있는지 확인
        private bool Is_SPGroup_Exist(string GroupName, SPWeb _web)
        {
            SPGroupCollection siteGroups = _web.SiteGroups;
            foreach (SPGroup group in siteGroups)
            {
                if (group.Name == GroupName)
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// Web에 기본 Group를 생성하고, 권한 상속을 제거.
        /// </summary>
        public void CreateGroup(string SiteID, string WebID, string adAdminGroupName, string adMemberGroupName, string AdminGroupPrefix, string MemberGroupPrefix)
        {
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite _site = new SPSite(new Guid(SiteID)))
                {
                    using (SPWeb spWeb = _site.OpenWeb(new Guid(WebID)))
                    {
                        string strSpAdminGroupName = AdminGroupPrefix + " Owner";
                        string strSPMemberGroupName = MemberGroupPrefix + "Member";

                        spWeb.AllowUnsafeUpdates = true;

                        //권한 상속 제거
                        spWeb.BreakRoleInheritance(false);

                        //AD그룹을 SPUser 객체에 담아둔다.
                        SPUser adAdminGroup = spWeb.EnsureUser(adAdminGroupName);
                        SPUser adMemberGroup = spWeb.EnsureUser(adMemberGroupName);

                        SPGroupCollection siteGroups = spWeb.SiteGroups;
                        siteGroups.Add(strSpAdminGroupName, _site.Owner, null, "Owner");
                        siteGroups.Add(strSPMemberGroupName, _site.Owner, null, "Member");
                        SPGroup spGroup = spWeb.SiteGroups[strSpAdminGroupName];
                        SPGroup spGroupMember = spWeb.SiteGroups[strSPMemberGroupName];
                        spWeb.AssociatedGroups.Add(spGroup);
                        spWeb.AssociatedGroups.Add(spGroupMember);

                        SPRoleAssignmentCollection roleAssignments = spWeb.RoleAssignments;
                        SPRoleAssignment assignmentEdit = new SPRoleAssignment(spGroup);
                        SPRoleAssignment assignmentEditMember = new SPRoleAssignment(spGroupMember);
                        assignmentEdit.RoleDefinitionBindings.Add(spWeb.RoleDefinitions.GetByType(SPRoleType.Administrator));
                        assignmentEditMember.RoleDefinitionBindings.Add(spWeb.RoleDefinitions.GetByType(SPRoleType.Contributor));
                        roleAssignments.Add(assignmentEdit);
                        roleAssignments.Add(assignmentEditMember);

                        //AD그룹을 SP그룹에 추가 하기.
                        spGroup.AddUser(adAdminGroup);
                        spGroupMember.AddUser(adMemberGroup);

                        spWeb.AllowUnsafeUpdates = false;
                    }
                }

            });


        }

        #endregion
    }
}
